﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using SW.Lite.IDAL;
using SW.Lite.Infrastructure.Common;
using SW.Lite.Infrastructure.Helpers;
using SW.Lite.Model;

namespace SW.Lite.DAL.MsSql
{
    public class Column : IColumn
    {
        public bool Exists(int id)
        {
            const string sql = "SELECT COUNT(1) FROM SW_Column WHERE Id=@Id";
            var paramter = new SqlParameter("@Id", SqlDbType.Int) { Value = id };
            return MsSqlHelper.Exists(sql, paramter);
        }

        public ColumnModel Single(int id)
        {
            const string sql =
                "SELECT Id, ParentID, Code, Title, Type, ModelId, ModelCode, NavigateUrl, Sort FROM SW_Column WHERE Id=@Id";

            var paramter = new SqlParameter("@Id", SqlDbType.Int) { Value = id };
            return FillSingle(sql, paramter);
        }

        public PageList<ColumnModel> All()
        {
            const string sql =
                "SELECT Id, ParentID, Code, Title, Type, ModelId, ModelCode, NavigateUrl, Sort FROM SW_Column";

            return FillList(sql);
        }

        public PageList<ColumnModel> All(int pageIndex, int pageSize)
        {
            var start = (pageIndex - 1) * pageSize;
            var end = pageIndex * pageSize;

            var sql = string.Format(@"SELECT Id, ParentID, Code, Title, Type, ModelId, ModelCode, NavigateUrl, Sort FROM (SELECT ROW_NUMBER() OVER (ORDER BY Id) ROWNO,* FROM SW_Column) A WHERE A.ROWNO BETWEEN {0} AND {1};
SELECT COUNT(1) FROM SW_Column;", start, end);

            return FillList(sql);
        }

        public ExecuteResult Insert(ColumnModel model)
        {
            const string sql =
                "INSERT INTO SW_Column(ParentID, Code, Title, Type, ModelId, ModelCode, NavigateUrl, Sort) VALUES (@ParentID, @Code, @Title, @Type, @ModelId, @ModelCode, @NavigateUrl, @Sort)";

            var parameters = new[]
            {
                new SqlParameter("@ParentID", SqlDbType.Int) {Value = model.ParentID},
                new SqlParameter("@Code", SqlDbType.NVarChar) {Value = model.Code},
                new SqlParameter("@Title", SqlDbType.NVarChar) {Value = model.Title},
                new SqlParameter("@Type", SqlDbType.Int) {Value = model.Type},
                new SqlParameter("@ModelId", SqlDbType.Int) {Value = model.ModelId},
                new SqlParameter("@ModelCode", SqlDbType.NVarChar) {Value = model.ModelCode},
                new SqlParameter("@NavigateUrl", SqlDbType.NVarChar) {Value = model.NavigateUrl},
                new SqlParameter("@Sort", SqlDbType.Int) {Value = model.Sort}
            };

            var result = MsSqlHelper.Execute(sql, parameters);
            return 0 == result ? new ExecuteResult(false) : new ExecuteResult(true);
        }

        public ExecuteResult Update(ColumnModel model)
        {
            const string sql =
                "UPDATE SW_Column SET ParentID=@ParentID, Code=@Code, Title=@Title, Type=@Type, ModelId=@ModelId, ModelCode=@ModelCode, NavigateUrl=@NavigateUrl, Sort=@Sort WHERE Id=@Id ";
            var parameters = new[]
            {
                new SqlParameter("@Id", SqlDbType.Int) {Value = model.Id},
                new SqlParameter("@ParentID", SqlDbType.Int) {Value = model.ParentID},
                new SqlParameter("@Code", SqlDbType.NVarChar) {Value = model.Code},
                new SqlParameter("@Title", SqlDbType.NVarChar) {Value = model.Title},
                new SqlParameter("@Type", SqlDbType.Int) {Value = model.Type},
                new SqlParameter("@ModelId", SqlDbType.Int) {Value = model.ModelId},
                new SqlParameter("@ModelCode", SqlDbType.NVarChar) {Value = model.ModelCode},
                new SqlParameter("@NavigateUrl", SqlDbType.NVarChar) {Value = model.NavigateUrl},
                new SqlParameter("@Sort", SqlDbType.Int) {Value = model.Sort}
            };

            var result = MsSqlHelper.Execute(sql, parameters);
            return 0 == result ? new ExecuteResult(false) : new ExecuteResult(true);
        }

        public ExecuteResult Delete(int id)
        {
            const string sql = "DELETE FROM SW_Column WHERE Id=@Id";
            var paramter = new SqlParameter("@Id", SqlDbType.Int) { Value = id };
            var result = MsSqlHelper.Execute(sql, paramter);
            return 0 == result ? new ExecuteResult(false) : new ExecuteResult(true);
        }

        public PageList<ColumnModel> GetTops()
        {
            const string sql =
                "SELECT Id, ParentID, Code, Title, Type, ModelId, ModelCode, NavigateUrl, Sort FROM SW_Column WHERE ParentID=0";

            return FillList(sql);
        }

        public ExecuteResult SaveSorts(Dictionary<int, int> columnSorts)
        {
            var sb = new StringBuilder();
            foreach (var kv in columnSorts)
                sb.AppendLine("UPDATE SW_Column SET Sort=" + kv.Value + " WHERE Id=" + kv.Key);

            var result = MsSqlHelper.Execute(sb.ToString());
            return 0 == result ? new ExecuteResult(false) : new ExecuteResult(true);
        }

        public PageList<ColumnModel> GetChildren(int columnId)
        {
            const string sql = "SELECT Id, ParentID, Code, Title, Type, ModelId, ModelCode, NavigateUrl, Sort FROM SW_Column WHERE ParentID=@ParentID;";

            var paramter = new SqlParameter("@ParentID", SqlDbType.Int) { Value = columnId };

            return FillList(sql, paramter);
        }

        private ColumnModel FillSingle(string sql, params SqlParameter[] paramters)
        {
            ColumnModel data = null;
            using (var rdr = MsSqlHelper.ExecuteReader(sql, paramters))
            {
                if (rdr.Read())
                    data = ReadData(rdr);
            }
            return data;
        }

        private PageList<ColumnModel> FillList(string sql, params SqlParameter[] paramters)
        {
            var list = new PageList<ColumnModel>();
            using (var rdr = MsSqlHelper.ExecuteReader(sql, paramters))
            {
                while (rdr.Read())
                    list.Add(ReadData(rdr));
                if (rdr.NextResult() && rdr.Read())
                    list.AllCount = rdr.GetInt32(0);
                else
                    list.AllCount = list.Count;
            }
            return list;
        }

        private ColumnModel ReadData(SqlDataReader dr)
        {
            return new ColumnModel
            {
                Id = Convert.ToInt32(dr["Id"]),
                ParentID = Convert.ToInt32(dr["ParentID"]),
                Code = Convert.ToString(dr["Code"]),
                Title = Convert.ToString(dr["Title"]),
                Type = Convert.ToInt32(dr["Type"]),
                ModelId = Convert.ToInt32(dr["ModelId"]),
                ModelCode = Convert.ToString(dr["ModelCode"]),
                NavigateUrl = Convert.ToString(dr["NavigateUrl"]),
                Sort = Convert.ToInt32(dr["Sort"])
            };
        }
    }
}